-- @owner: lihongji
-- @date: 2022/07/29
-- @testpoint: from where 子查询，部分合理报错

--step1:建表并插入数据;expect:成功
drop table if exists t_percent_rank_001;
create table t_percent_rank_001(id1 int,id2 int,id3 smallint,id4 int,num1 float,char1 varchar(500)) with (storage_type=ustore);
insert into t_percent_rank_001 values ( 1 , -1 , 2 , 5 , 4041.04329387 , ' a哪吒 ' );
insert into t_percent_rank_001 values ( 3 , 3 , 6 , 3 , 4005.70237548 , ' 010012228 ' );
insert into t_percent_rank_001 values ( 1 , -1 , 3 , 3 , 4030.1242131 , ' 数字字符字母 ' );
insert into t_percent_rank_001 values ( 1 , 4 , 4 , 4 , 4001.87754238 , ' 010012228 ' );
insert into t_percent_rank_001 values ( 3 , 4 , 4 , 0 , 4046.25584633 , ' 数字字符字母 ' );
insert into t_percent_rank_001 values ( 2 , -2 , 6 , 4 , 4003.68558645 , ' 数字字符字母 ' );
insert into t_percent_rank_001 values ( 1 , -2 , 2 , 3 , 4044.9145853 , '' );
insert into t_percent_rank_001 values ( 2 , 4 , 9 , 0 , 4035.26588922 , ' 010012228 ' );
insert into t_percent_rank_001 values ( 3 , -2 , 1 , 2 , 4011.51183805 , ' 010012228 ' );
insert into t_percent_rank_001 values ( 1 , 2 , 10 , 2 , 4018.35133361 , ' 数字字符字母 ' );
insert into t_percent_rank_001 values ( 1 , -2 , 7 , 3 , 4041.62109974 , ' ilhfjejgyhg ' );
insert into t_percent_rank_001 values ( 3 , 4 , 10 , 1 , 4018.19233257 , ' 数字字符字母 ' );
insert into t_percent_rank_001 values ( 1 , 1 , 5 , 0 , 4018.75686387 , ' 010012228 ' );
insert into t_percent_rank_001 values ( 1 , -2 , 9 , -1 , 4001.50542863 , ' gkj^%$ ( )+_-= ' );
insert into t_percent_rank_001 values ( 5 , -2 , 2 , 5 , 4046.20763052 , ' ilhfjejgyhg ' );
insert into t_percent_rank_001 values ( 7 , 4 , 1 , 3 , 4032.28411454 , ' a哪吒 ' );
insert into t_percent_rank_001 values ( 6 , 34 , 4 , 3 , 4016.94485186 , ' gkj^%$ ( )+_-= ' );
insert into t_percent_rank_001 values ( 8 , 1 , 55 , -1 , 4007.07989431 , ' 数字字符字母 ' );
insert into t_percent_rank_001 values ( 6 , 3 , 87 , 0 , 4035.51077643 , ' ilhfjejgyhg ' );
insert into t_percent_rank_001 values ( 0 , 3 , 9 , 4 , 4004.05504939 , ' ilhfjejgyhg ' );
insert into t_percent_rank_001 values ( 10 , 2 , 1 , 2 , 4020.02593705 , ' ilhfjejgyhg ' );
insert into t_percent_rank_001 values ( null , null , 9 , null , 4004.05504939 , null );
insert into t_percent_rank_001 values ( null , null , null , null , null , ' ilhfjejgyhg ' );
insert into t_percent_rank_001 values ( null , null , null , null , null , null );

--step2:建表并插入数据;expect:成功
drop table if exists t_percent_rank_003;
create table t_percent_rank_003 with (storage_type=ustore) as select * from t_percent_rank_001;
insert into t_percent_rank_003 select * from t_percent_rank_003;
insert into t_percent_rank_003 values ( 1 , -1 , null , 5 , 4041.04329387 , ' a哪吒 ' );
insert into t_percent_rank_003 values ( 3 , null, 6 , 3 , 4005.70237548 , null );
insert into t_percent_rank_003 values ( 4 , null , 3 , 3 , 4030.1242131 , ' 数字字符字母 ' );
insert into t_percent_rank_003 values ( 1 , -244 , 4 , null , null , ' 010012228 ' );
insert into t_percent_rank_003 values ( null , 4 , 4 , 0 , 4046.25584633 , ' 数字字符字母 ' );
insert into t_percent_rank_003 values ( 2 , -2 , null , 4 , null , ' 数字字符字母 ' );
insert into t_percent_rank_003 values ( null , -2 , 2 , 3 , 4044.9145853 , ' 数字字符字母 ' );
insert into t_percent_rank_003(id1) values ( 9);

--step3:建序列;expect:成功
drop sequence if exists v_seq;
create sequence v_seq increment by 1 start with 10000;

--step4:建表并插入数据;expect:成功
drop table if exists t_percent_rank_004;
create table t_percent_rank_004(col_1 bigint, col_2 timestamp, col_3 bool,col_4 decimal,col_5 clob ,
col_6 interval day to second ,col_7 nchar(30) ,col_8 blob ,col_9 raw(100),col_10 date,
col_11 bytea ,col_12 bytea,col_13 varchar2(800)) with (storage_type=ustore);

insert into t_percent_rank_004 values(
v_seq.nextval,to_date('2019-09-05 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'true',3.141592665735674554784786,lpad('abc','50','a@123&^%djgk'),
(interval '4 5:12:10.222' day to second(3)),rpad('abc','30','uegc%^4'),'101001010','0faadb9',to_date('1981-2-20', 'yyyy-mm-dd'),
'101001010','01010111111110000000000',lpad('jhafd韩子高各机构测','80','ajge'));
insert into t_percent_rank_004 values(
v_seq.nextval,to_date('2019-09-06 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'true',3.141592665735674554784787,lpad('abc','55','a@123&^%djgk'),
(interval '4 5:12:10.222' day to second(3)),rpad('ab','30','uegc%^4'),'101010101010','0faadb9',to_date('1981-2-21', 'yyyy-mm-dd'),
'1001010101010','01010111111110000000001100000',lpad('jhafd韩子高各机构测','82','ajgedf'));
insert into t_percent_rank_004 values(
v_seq.nextval,to_date('2019-09-07 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'true',3.141592665735674554784788,lpad('abc','100','a@123&^%djgk'),
(interval '4 5:12:10.222' day to second(3)),rpad('abc','29','uegc%^4'),'101010101010','0faadb9',to_date('1981-3-20', 'yyyy-mm-dd'),
'1010100101010','010101111111100000000011100000',lpad('jhafd韩子高各机构测','81','ajge'));
insert into t_percent_rank_004 values(
v_seq.nextval,to_date('2019-09-08 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'false',3.141592665735674554784789,lpad('abc','500','a@123&^%djgk'),
(interval '4 5:12:10.222' day to second(3)),rpad('abc','28','uegc%^4'),'1010101001010','0faadb9',to_date('1981-3-21', 'yyyy-mm-dd'),
'10101001010111','010101111111100000000000000',lpad('jhafd韩子高各机构测','185','ajge'));

insert into t_percent_rank_004 values(
v_seq.nextval,to_date('2019-09-05 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'true',3.1415926657356745547847861,lpad('abc','20','a@123&^%djgk'),
(interval '4 5:13:10.222' day to second(3)),rpad('abc','30','uegc%^4'),empty_blob(),'0faadb9',to_date('1981-2-20', 'yyyy-mm-dd'),
'10101001010','01010111111110000000000',lpad('jhafd韩子h高各机构测','80','ajge'));
insert into t_percent_rank_004 values(
v_seq.nextval,to_date('2019-09-06 10:45:42', 'yyyy-mm-dd hh:mi:ss'),null,3.1415926657356745547847870,lpad('abc','55','a@123&^%djgk'),
(interval '5 5:12:10.222' day to second(2)),rpad('ab','30','uegc%^4'),'10101001010','0faadb9',to_date('1981-2-21', 'yyyy-mm-dd'),
'101010010101','01010111111110000000001100000',lpad('jhafd韩子#￥%……￥……%*','82','ajgedf'));
insert into t_percent_rank_004 values(
v_seq.nextval,to_date('2019-09-07 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'false',3.1415926657356745547847880,lpad('abc','30','a@123&^%djgk'),
(interval '4 5:10:10.222' day to second(2)),rpad('abc','29','uegc%^4'),'10101001010','0faadb9',to_date('1981-3-20', 'yyyy-mm-dd'),
'1010100101010','01010111111110000000001100000',lpad('jhafd韩子高各机构测','81','ajge'));
insert into t_percent_rank_004 values(
v_seq.nextval,to_date('2019-09-08 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'false',3.1415926657356745547847890,lpad('abc','500','a@123&^%djgk'),
(interval '6 5:11:10.222' day to second(4)),rpad('abc','28','uegc%^4'),empty_blob(),'0faadb9',to_date('1981-3-21', 'yyyy-mm-dd'),
'10101001010111','010101111111100000000000000',lpad('江苏高考覆盖扩容','185','ajge'));

insert into t_percent_rank_004 values(
v_seq.nextval,null,'true',null,lpad('abc','50','a@123&^%djgk'),
(interval '4 5:12:10.222' day to second(3)),rpad('abc','30','uegc%^4'),'10101001010','0faadb923',null,
'10101001010','01010111111110000000000',null);
insert into t_percent_rank_004 values(
v_seq.nextval,to_date('2019-09-06 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'true',null,lpad('abc','55','a@123&^%djgk'),
(interval '4 5:12:10.222' day to second(3)),rpad('ab','30','uegc%^4'),'10101001010',null,to_date('1982-2-21', 'yyyy-mm-dd'),
'101010010101',null,lpad('韩子高各机构测','82','ajgedf'));
insert into t_percent_rank_004 values(
v_seq.nextval,to_date('2019-09-07 10:45:42', 'yyyy-mm-dd hh:mi:ss'),null,3.1415926657356745547847884,lpad('abc','100','a@123&^%djgk'),
null,rpad('abc','29','uegc%^4'),'10101001010','0faadb900',to_date('1981-3-20', 'yyyy-mm-dd'),
null,'010101111111100000000011100000',lpad('jhafd韩子高各机构测','81','ajge'));
insert into t_percent_rank_004 values(
v_seq.nextval,to_date('2019-09-08 10:45:42', 'yyyy-mm-dd hh:mi:ss'),'false',3.141592665735674554784789,null,
(interval '4 5:12:10.222' day to second(3)),null,'10101001010','0faadb9011',null,
'101001010111','010101111111100000000000000',lpad('abc','35','a@123&^%djgk'));
insert into t_percent_rank_004(col_1) values(v_seq.nextval-1);
insert into t_percent_rank_004(col_9) values(null);

--step5:查询;expect:部分合理报错
select id1,id3,char1 from
(select id1,id3,char1,percent_rank() over(partition by id1,char1 order by id3 nulls first) f1 from t_percent_rank_001 )
where (percent_rank() over(partition by id1,char1 order by id3 nulls first)) =1;

select id1,id3,char1 from
(select id1,id3,char1,percent_rank() over(partition by id1,char1 order by id3 nulls first) f1 from t_percent_rank_001 ) where f1 =1;
with tmp1 as (select col_1,col_3,col_7,lengthb(col_13),col_13,percent_rank() over(partition by col_3,col_7 order by col_13 desc nulls last) f1 from t_percent_rank_004)
select col_1,col_7 from tmp1 where (percent_rank() over(partition by col_3,col_7 order by col_13 desc nulls last)) >1;

with tmp1 as (select col_1,col_3,col_7,lengthb(col_13),percent_rank() over(partition by col_3,col_7 order by col_13 desc nulls last) f1 from t_percent_rank_004)
select col_1,col_7 from tmp1 where f1 >1;

with tmp2 as
(select distinct id1,id2,id3,percent_rank() over(partition by id1+id2,id2 order by id2+22 nulls last,id3*(-3) desc nulls last) f1 from t_percent_rank_003)
select id1+id2,id2,id3*(-3),f1 from tmp2 where f1 > 1 order by id1+id2,id2,id3*(-3),f1;

--step6:清理环境;expect:成功
drop table if exists t_percent_rank_001;
drop table if exists t_percent_rank_003;
drop sequence if exists v_seq;
drop table if exists t_percent_rank_004;